package com.etc.dao.impl;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.catalina.ha.backend.Sender;

import com.etc.dao.MusicDao;
import com.etc.entity.Album;
import com.etc.entity.Languages;
import com.etc.entity.Mood;
import com.etc.entity.MusicInfo;
import com.etc.entity.MusicLyric;
import com.etc.entity.MusicMp3;
import com.etc.entity.MusicStyle;
import com.etc.entity.MusicVideo;
import com.etc.entity.Scene;
import com.etc.entity.Singger;
import com.etc.entity.Theme;
import com.etc.entity.UserInfo;
import com.etc.util.DBUtil;

import sun.print.resources.serviceui;

/**
 * 歌曲Dao实现类
 * @author nxlnian
 * @author Amber
 *
 */
public class MusicDaoImpl implements MusicDao{
	
	@Override
	public List<MusicInfo> findBysongSleetId(Integer broadcastingStationId) {
		List<MusicInfo> musicInfos = new ArrayList<MusicInfo>();
		StringBuilder sb = new StringBuilder();
		sb.append("select A.music_id,")
		  .append(" B.music_name,")
		  .append(" B.duration,")
		  .append(" D.singger_id,")
		  .append(" D.singger_name,")
		  .append(" C.video_id from")
		  .append(" ((broadcasting_station_details A inner join music_info B on A.music_id = B.music_id) ")
		  .append(" left join music_video C on A.music_id = C.music_id)")
		  .append(" left join singger D on B.singger_id = D.singger_id ")
		  .append(" where A.broadcasting_station_id = ? limit 0,5");
		ResultSet rs = DBUtil.query(sb.toString(),broadcastingStationId);
		try {
			while (rs.next()) {
				MusicInfo musicInfo = new MusicInfo();
				musicInfo.setMusicId(rs.getInt("music_id"));
				musicInfo.setMusicName(rs.getString("music_name"));
				musicInfo.setDuration(rs.getString("duration"));
				Singger singger = new Singger();
				singger.setSinggerId(rs.getInt("singger_id"));
				singger.setSinggerName(rs.getString("singger_name"));
				musicInfo.setSingger(singger);
				MusicVideo musicVideo = new MusicVideo();
				musicVideo.setVideoId(rs.getInt("video_id"));
				musicInfo.setMusicVideo(musicVideo);
				musicInfos.add(musicInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return musicInfos;
	}
	
	@Override
	public List<MusicInfo> findByUesrId(Integer userId) {
		List<MusicInfo> musicInfos = new ArrayList<MusicInfo>();
		StringBuilder sb = new StringBuilder();
		sb.append("select A.music_id,")
		  .append(" B.music_name,")
		  .append(" B.duration,")
		  .append(" D.singger_id,")
		  .append(" D.singger_name,")
		  .append(" C.video_id from")
		  .append(" ((music_collection A inner join music_info B on A.music_id = B.music_id) ")
		  .append(" left join music_video C on A.music_id = C.music_id)")
		  .append(" left join singger D on B.singger_id = D.singger_id ")
		  .append(" where A.user_id = ? limit 0,5");
		ResultSet rs = DBUtil.query(sb.toString(),userId);
		try {
			while (rs.next()) {
				MusicInfo musicInfo = new MusicInfo();
				musicInfo.setMusicId(rs.getInt("music_id"));
				musicInfo.setMusicName(rs.getString("music_name"));
				musicInfo.setDuration(rs.getString("duration"));
				Singger singger = new Singger();
				singger.setSinggerId(rs.getInt("singger_id"));
				singger.setSinggerName(rs.getString("singger_name"));
				musicInfo.setSingger(singger);
				MusicVideo musicVideo = new MusicVideo();
				musicVideo.setVideoId(rs.getInt("video_id"));
				musicInfo.setMusicVideo(musicVideo);
				musicInfos.add(musicInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return musicInfos;
	}

	@Override
	public MusicInfo findByid(Integer musicId) {
		MusicInfo musicInfo = null;
		
		StringBuilder sb = new StringBuilder();
		sb.append("select A.music_name,")
		.append(" B.singger_name,")
		.append(" C.music_lyric_name,")
		.append(" A.music_img_path,")
		.append(" A.music_mp3_path")
		.append(" from (music_info A left join singger B on A.singger_id = B.singger_id)")
		.append(" left join music_lyric C on A.music_lyric_id = C.music_lyric_id")
		.append(" where A.music_id = ?");

		ResultSet rs = DBUtil.query(sb.toString(),musicId);
		try {
			while (rs.next()) {
				musicInfo = new MusicInfo();
				
				musicInfo.setMusicName(rs.getString("music_name"));
				musicInfo.setMusicImgPath(rs.getString("music_img_path"));
				//获取歌手对象
				Singger singger = new Singger();
				singger.setSinggerName(rs.getString("singger_name"));
				musicInfo.setSingger(singger);
				//获取歌词
				MusicLyric musicLyric = new MusicLyric();
				musicLyric.setMusicLyrName(rs.getString("music_lyric_name"));
				musicInfo.setMusicLyric(musicLyric);
				//获取歌曲文件名
				MusicMp3 musicMp3 = new MusicMp3();
				musicMp3.setMusicMp3Path(rs.getString("music_mp3_path"));
				musicInfo.setMusicMp3(musicMp3);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return musicInfo;
	}
	// ============================================ admin 操作 Dao ======================================

	@Override
	public int addSong(MusicInfo musicInfo) {
		StringBuilder sb = new StringBuilder();
		sb.append("INSERT INTO music_info(music_id,music_name,initials")
		.append(",singger_id")
		.append(",album_id")
		.append(",style_id")
		.append(",release_time")
		.append(",theme_id")
		.append(",mood_id")
		.append(",scene_id")
		.append(",languages_id")
		.append(",music_lyric_id")
		.append(",music_img_path")
		.append(",music_mp3_path")
		.append(",duration")
		.append(") VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		return DBUtil.update(sb.toString(), musicInfo.getMusicId(),musicInfo.getMusicName(),musicInfo.getInitials(),
				musicInfo.getSingger().getSinggerId(),musicInfo.getAlbum().getAlbumId(),musicInfo.getMusicStyle().getStyleId(),
				musicInfo.getReleaseTime(),musicInfo.getTheme().getThemeId(),musicInfo.getMood().getMoodId(),
				musicInfo.getScene().getSceneId(),musicInfo.getLanguages().getLanguagesId(),musicInfo.getMusicLyric().getMusicLyrId(),
				musicInfo.getMusicImgPath(),musicInfo.getMusicMp3Path(),musicInfo.getDuration());
	}

	@Override
	public int modifySingerInfo(MusicInfo musicInfo) {
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE music_info SET music_id=?,music_name=?,initials=?");
		if (musicInfo.getSingger().getSinggerId() != 0) {
			sql.append(",singger_id="+musicInfo.getSingger().getSinggerId());
		}
		if (musicInfo.getAlbum().getAlbumId() != 0) {
			sql.append(",album_id="+musicInfo.getAlbum().getAlbumId());
		}
		if (musicInfo.getMusicStyle().getStyleId() != 0) {
			sql.append(",style_id="+musicInfo.getMusicStyle().getStyleId());
		}
		if (musicInfo.getReleaseTime() != null && !"".equals(musicInfo.getReleaseTime())) {
			sql.append(",release_time='"+musicInfo.getReleaseTime()+"'");
		}
		if (musicInfo.getTheme().getThemeId() != 0) {
			sql.append(",theme_id="+musicInfo.getTheme().getThemeId());
		}
		if (musicInfo.getMood().getMoodId() != 0) {
			sql.append(",mood_id="+musicInfo.getMood().getMoodId());
		}
		if (musicInfo.getScene().getSceneId() != 0) {
			sql.append(",scene_id="+musicInfo.getScene().getSceneId());
		}
		if (musicInfo.getLanguages().getLanguagesId() != 0) {
			sql.append(",languages_id="+musicInfo.getLanguages().getLanguagesId());
		}
		if (musicInfo.getMusicLyric() != null) {
			sql.append(",music_lyric_id="+musicInfo.getMusicLyric().getMusicLyrId());
		}
		if (musicInfo.getMusicImgPath() != null) {
			sql.append(",music_img_path='"+musicInfo.getMusicImgPath()+"'");
		}
		if (musicInfo.getMusicMp3Path() != null) {
			sql.append(",music_mp3_path='"+musicInfo.getMusicMp3Path()+"'");
		}
		sql.append(" where music_id =?");
		return DBUtil.update(sql.toString(), musicInfo.getMusicId(),musicInfo.getMusicName(),musicInfo.getInitials(),musicInfo.getMusicId());
	}

	@Override
	public int deleteSongById(Integer musicId) {
		return DBUtil.update("delete from music_info where music_id = ?", musicId);
	}

	@Override
	public MusicInfo findSongInfoById(Integer musicId) {
		MusicInfo musicInfo=null;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ")
		.append("A.music_id,A.music_name,A.initials,")
		.append("B.singger_id,B.singger_name,")
		.append("C.album_id,C.album_name,")
		.append("D.style_id,D.style_name,")
		.append("A.release_time,")
		.append("E.theme_id,E.theme_name,")
		.append("F.mood_id,F.mood_name,")
		.append("G.scene_id,G.scene_name,")
		.append("H.languages_id,H.languages_name,")
		.append("A.create_time,A.like_number,A.play_volume,A.music_img_path")
		.append(" FROM music_info A left JOIN singger B ON A.singger_id=B.singger_id")
		.append(" left JOIN album C ON A.album_id = C.album_id")
		.append(" left JOIN music_style D ON A.style_id = D.style_id")
		.append(" left JOIN theme E ON A.theme_id = E.theme_id")
		.append(" left JOIN mood F ON A.mood_id = F.mood_id")
		.append(" left JOIN scene G ON A.scene_id = G.scene_id")
		.append(" left JOIN languages H ON A.languages_id = H.languages_id")
		.append(" where A.music_id = ? ");
		
		ResultSet rs = DBUtil.query(sql.toString(),musicId);
		try {
			while (rs.next()) {
				musicId = rs.getInt("music_id");
				String musicName = rs.getString("music_name");
				String initials  = rs.getString("initials");
				Integer singgerId= rs.getInt("singger_id");
				String singgerName  = rs.getString("singger_name");
				Integer albumId = rs.getInt("album_id");
				String albumName  = rs.getString("album_name");
				Integer styleId = rs.getInt("style_id");
				String styleName  = rs.getString("style_name");
				String releaseTime = rs.getString("release_time");
				Integer themeId = rs.getInt("theme_id");
				String themeName  = rs.getString("theme_name");
				Integer moodId = rs.getInt("mood_id");
				String moodName  = rs.getString("mood_name");
				Integer sceneId = rs.getInt("scene_id");
				String sceneName  = rs.getString("scene_name");
				Integer languagesId = rs.getInt("languages_id");
				String languagesName  = rs.getString("languages_name");
				String createTim = rs.getString("create_time");
				Integer likeNumber = rs.getInt("like_number");
				Integer playVolume = rs.getInt("play_volume");
				String musicImgPath = rs.getString("music_img_path");
				
				musicInfo = new MusicInfo();
				musicInfo.setMusicId(musicId);
				musicInfo.setMusicName(musicName);
				musicInfo.setInitials(initials);
				Singger singger =  new Singger();
				singger.setSinggerId(singgerId);
				singger.setSinggerName(singgerName);
				musicInfo.setSingger(singger );
				Album album = new Album();
				album.setAlbumId(albumId);
				album.setAlbumName(albumName);
				musicInfo.setAlbum(album);
				MusicStyle musicStyle = new MusicStyle();
				musicStyle.setStyleId(styleId);
				musicStyle.setStyleName(styleName);
				musicInfo.setMusicStyle(musicStyle);
				Theme theme = new Theme();
				theme.setThemeId(themeId);
				theme.setThemeName(themeName);
				musicInfo.setTheme(theme);
				Mood mood = new Mood();
				mood.setMoodId(moodId);
				mood.setMoodName(moodName);
				musicInfo.setMood(mood);
				Scene scene = new Scene();
				scene.setSceneId(sceneId);
				scene.setSceneName(sceneName);
				musicInfo.setScene(scene);
				Languages languages = new Languages();
				languages.setLanguagesId(languagesId);
				languages.setLanguagesName(languagesName);
				musicInfo.setLanguages(languages);
				musicInfo.setReleaseTime(releaseTime);
				musicInfo.setCreateTim(createTim);
				musicInfo.setLikeNumber(likeNumber);
				musicInfo.setPlayVolume(playVolume);
				musicInfo.setMusicImgPath(musicImgPath);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return musicInfo;
	}

	@Override
	public List<MusicInfo> findSongInfoInfoBySongName(String musicName,int pageNum,int pageSize) {
		List<MusicInfo> list = new ArrayList<MusicInfo>();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ")
		.append("A.music_id,A.music_name,A.initials,")
		.append("B.singger_id,B.singger_name,")
		.append("C.album_id,C.album_name,")
		.append("D.style_id,D.style_name,")
		.append("A.release_time,")
		.append("E.theme_id,E.theme_name,")
		.append("F.mood_id,F.mood_name,")
		.append("G.scene_id,G.scene_name,")
		.append("H.languages_id,H.languages_name,")
		.append("A.create_time,A.like_number,A.play_volume,A.music_img_path")
		.append(" FROM music_info A left JOIN singger B ON A.singger_id=B.singger_id")
		.append(" left JOIN album C ON A.album_id = C.album_id")
		.append(" left JOIN music_style D ON A.style_id = D.style_id")
		.append(" left JOIN theme E ON A.theme_id = E.theme_id")
		.append(" left JOIN mood F ON A.mood_id = F.mood_id")
		.append(" left JOIN scene G ON A.scene_id = G.scene_id")
		.append(" left JOIN languages H ON A.languages_id = H.languages_id");
		
		sql.append(" where 1 = 1");
		
		if (musicName != null && !"".equals(musicName)) {
			sql.append(" and A.music_name like  '%" + musicName + "%'");
		}
		sql.append(" limit " +  (pageNum - 1) * pageSize + "," + pageSize);
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while (rs.next()) {
				
				Integer musicId = rs.getInt("music_id");
				musicName = rs.getString("music_name");
				String initials  = rs.getString("initials");
				Integer singgerId= rs.getInt("singger_id");
				String singgerName  = rs.getString("singger_name");
				Integer albumId = rs.getInt("album_id");
				String albumName  = rs.getString("album_name");
				Integer styleId = rs.getInt("style_id");
				String styleName  = rs.getString("style_name");
				String releaseTime = rs.getString("release_time");
				Integer themeId = rs.getInt("theme_id");
				String themeName  = rs.getString("theme_name");
				Integer moodId = rs.getInt("mood_id");
				String moodName  = rs.getString("mood_name");
				Integer sceneId = rs.getInt("scene_id");
				String sceneName  = rs.getString("scene_name");
				Integer languagesId = rs.getInt("languages_id");
				String languagesName  = rs.getString("languages_name");
				String createTim = rs.getString("create_time");
				Integer likeNumber = rs.getInt("like_number");
				Integer playVolume = rs.getInt("play_volume");
				String musicImgPath = rs.getString("music_img_path");
				
				MusicInfo musicInfo = new MusicInfo();
				musicInfo = new MusicInfo();
				musicInfo.setMusicId(musicId);
				musicInfo.setMusicName(musicName);
				musicInfo.setInitials(initials);
				Singger singger =  new Singger();
				singger.setSinggerId(singgerId);
				singger.setSinggerName(singgerName);
				musicInfo.setSingger(singger );
				Album album = new Album();
				album.setAlbumId(albumId);
				album.setAlbumName(albumName);
				musicInfo.setAlbum(album);
				MusicStyle musicStyle = new MusicStyle();
				musicStyle.setStyleId(styleId);
				musicStyle.setStyleName(styleName);
				musicInfo.setMusicStyle(musicStyle);
				Theme theme = new Theme();
				theme.setThemeId(themeId);
				theme.setThemeName(themeName);
				musicInfo.setTheme(theme);
				Mood mood = new Mood();
				mood.setMoodId(moodId);
				mood.setMoodName(moodName);
				musicInfo.setMood(mood);
				Scene scene = new Scene();
				scene.setSceneId(sceneId);
				scene.setSceneName(sceneName);
				musicInfo.setScene(scene);
				Languages languages = new Languages();
				languages.setLanguagesId(languagesId);
				languages.setLanguagesName(languagesName);
				musicInfo.setLanguages(languages);
				musicInfo.setReleaseTime(releaseTime);
				musicInfo.setCreateTim(createTim);
				musicInfo.setLikeNumber(likeNumber);
				musicInfo.setPlayVolume(playVolume);
				musicInfo.setMusicImgPath(musicImgPath);
				
				list.add(musicInfo);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public int countBySongName(String musicName) {
		int count=0;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT")
		.append(" count(*)")
		.append(" FROM music_info A left JOIN singger B ON A.singger_id=B.singger_id")
		.append(" left JOIN album C ON A.album_id = C.album_id")
		.append(" left JOIN music_style D ON A.style_id = D.style_id")
		.append(" left JOIN theme E ON A.theme_id = E.theme_id")
		.append(" left JOIN mood F ON A.mood_id = F.mood_id")
		.append(" left JOIN scene G ON A.scene_id = G.scene_id")
		.append(" left JOIN languages H ON A.languages_id = H.languages_id");
		
		sql.append(" where 1 = 1");
		
		if (musicName != null && !"".equals(musicName)) {
			sql.append(" and A.music_name like  '%" + musicName + "%'");
		}
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while (rs.next()) {
				count =  rs.getInt(1);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		
		return count;
	}

	@Override
	public List<Map<String, Object>> findLikeCountGrounpByStyle() {
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT B.style_name,")
		.append("TRUNCATE((SUM(like_number)/(SELECT SUM(like_number) FROM music_info)*100),2)")
		.append(" FROM music_info A INNER JOIN music_style B")
		.append(" ON A.style_id = B.style_id")
		.append(" GROUP BY A.style_id");
		
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while (rs.next()) {
				String styleName = rs.getString("style_name");
				BigDecimal countLikeNumber = rs.getBigDecimal(2);
				
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("styleName", styleName);
				map.put("countLikeNumber", countLikeNumber);
				
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	// ============================================ admin 操作 Dao ======================================
	

	@Override
	public List<MusicInfo> findMusicById(Integer userId) {
		List<MusicInfo> musicInfos = new ArrayList<MusicInfo>();
		
		StringBuilder sb = new StringBuilder();
		sb.append("select A.music_name,")
		.append(" B.singger_name,")
		.append(" C.music_lyric_name,")
		.append(" A.music_img_path,")
		.append(" A.music_mp3_path")
		.append(" from (music_info A left join singger B on A.singger_id = B.singger_id)")
		.append(" left join music_lyric C on A.music_lyric_id = C.music_lyric_id")
		.append(" where A.music_id = ?");

		ResultSet rs = DBUtil.query(sb.toString(),userId);
		try {
			while (rs.next()) {
				MusicInfo musicInfo = new MusicInfo();
				
				musicInfo.setMusicName(rs.getString("music_name"));
				musicInfo.setMusicImgPath(rs.getString("music_img_path"));
				//获取歌手对象
				Singger singger = new Singger();
				singger.setSinggerName(rs.getString("singger_name"));
				musicInfo.setSingger(singger);
				//获取歌词
				MusicLyric musicLyric = new MusicLyric();
				musicLyric.setMusicLyrName(rs.getString("music_lyric_name"));
				musicInfo.setMusicLyric(musicLyric);
				//获取歌曲文件名
				MusicMp3 musicMp3 = new MusicMp3();
				musicMp3.setMusicMp3Path(rs.getString("music_mp3_path"));
				musicInfo.setMusicMp3(musicMp3);
				
				musicInfos.add(musicInfo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return musicInfos;
	}
	@Override
	public List<MusicInfo> findMusicBySinggerId(Integer singgerId, int pageNum, int pageSize) {
        List<MusicInfo> listMusicInfo = new ArrayList<MusicInfo>();
		
		StringBuilder sb = new StringBuilder();
		sb.append("select ")
		.append(" A.music_id,")
		.append(" A.music_name,")
		.append(" A.music_mp3_path,")
		.append(" A.duration,")
		.append(" B.album_name,")
		.append(" C.video_id")
		.append(" from music_info A")
		.append(" left join album B on A.album_id=B.album_id")
		.append(" left join music_video C on A.music_id = C.music_id");
		
		
		// 动态SQL
				sb.append(" where A.singger_id="+singgerId);
				
				
				
				// 进行分页  select * from 表  where... limit a,b;
				sb.append(" limit " +  (pageNum - 1) * pageSize + "," + pageSize);

		ResultSet rs = DBUtil.query(sb.toString());
		try {
			while (rs.next()) {
				Integer musicId = rs.getInt("music_id");
				 String musicName = rs.getString("music_name");
				 String musicMp3Path = rs.getString("music_mp3_path");
				 String duration = rs.getString("duration");
				 String albumName = rs.getString("album_name");
				 Integer videoId = rs.getInt("video_id");
				// 歌曲对象
				 MusicInfo musicInfo = new MusicInfo();
				 musicInfo.setMusicId(musicId);
				 musicInfo.setMusicName(musicName);
				 musicInfo.setMusicMp3Path(musicMp3Path);
				 musicInfo.setDuration(duration);
				 Album album = new Album();
				 album.setAlbumName(albumName);
				 MusicVideo musicVideo = new MusicVideo();
				 musicVideo.setVideoId(videoId);
				 musicInfo.setAlbum(album);
				 musicInfo.setMusicVideo(musicVideo);
				// 集合
				 listMusicInfo.add(musicInfo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return listMusicInfo;
	}

	@Override
	public int countMusicBySinggerId(Integer singgerId) {
		// TODO Auto-generated method stub
		int count = 0;
		StringBuilder sb = new StringBuilder();
		sb.append("select ")
		.append(" count(*)")
		.append(" from music_info ");
		
		
		
		// 动态SQL
				sb.append(" where singger_id="+singgerId);
			ResultSet rs = DBUtil.query(sb.toString());
		try {
			while (rs.next()) {
				
				count =  rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return count;
	}

	@Override
	public List<MusicInfo> findByBroadcastingStationId(int broadcastingStationId) {
         List<MusicInfo> listMusicInfo = new ArrayList<MusicInfo>();
		StringBuilder sb = new StringBuilder();
		sb.append("select ")
		.append(" A.music_id,")
		.append(" A.music_name,")
		.append(" A.music_mp3_path,")
		.append(" E.video_id,")
		.append(" A.duration,")
		.append(" D.album_name,")
		.append(" C.singger_id,")
		.append(" C.singger_name")
		.append(" from music_info A")
		.append(" left join broadcasting_station_details B on A.music_id=B.music_id")
		.append(" left join singger C on A.singger_id=C.singger_id")
		.append(" left join album D on A.album_id=D.album_id")
		.append(" left join music_video E on A.music_id=E.music_id");
		
		
		// 动态SQL
				sb.append(" where B.broadcasting_station_id="+broadcastingStationId);
				ResultSet rs = DBUtil.query(sb.toString());
				try {
					while (rs.next()) {
						Integer musicId = rs.getInt("music_id");
						Integer singgerId = rs.getInt("singger_id");
						Integer videoId = rs.getInt("video_id");
						 String musicName = rs.getString("music_name");
						 String musicMp3Path = rs.getString("music_mp3_path");
						 String duration = rs.getString("duration");
						 String albumName = rs.getString("album_name");
						 String singgerName = rs.getString("singger_name");
						// 歌曲对象
						 MusicInfo musicInfo = new MusicInfo();
						 musicInfo.setMusicId(musicId);
						 musicInfo.setMusicName(musicName);
						 musicInfo.setMusicMp3Path(musicMp3Path);
						 musicInfo.setDuration(duration);
						 Album album = new Album();
						 album.setAlbumName(albumName);
						 musicInfo.setAlbum(album);
						 Singger singger = new Singger();
						 singger.setSinggerId(singgerId);
						 singger.setSinggerName(singgerName);
						 musicInfo.setSingger(singger);
						 MusicVideo musicVideo =new MusicVideo();
						 musicVideo.setMusicId(musicId);
						 musicInfo.setMusicVideo(musicVideo);
						// 集合
						 listMusicInfo.add(musicInfo);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return listMusicInfo;
	}
	@Override
	public List<MusicInfo> findAll() {
		List<MusicInfo> list = new ArrayList<MusicInfo>();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ")
		.append("A.music_id,A.music_name,A.initials,")
		.append("B.singger_id,B.singger_name,")
		.append("C.album_id,C.album_name,")
		.append("D.style_id,D.style_name,")
		.append("A.release_time,")
		.append("E.theme_id,E.theme_name,")
		.append("F.mood_id,F.mood_name,")
		.append("G.scene_id,G.scene_name,")
		.append("H.languages_id,H.languages_name,")
		.append("A.create_time,A.like_number,A.play_volume,A.music_img_path")
		.append(" FROM music_info A left JOIN singger B ON A.singger_id=B.singger_id")
		.append(" left JOIN album C ON A.album_id = C.album_id")
		.append(" left JOIN music_style D ON A.style_id = D.style_id")
		.append(" left JOIN theme E ON A.theme_id = E.theme_id")
		.append(" left JOIN mood F ON A.mood_id = F.mood_id")
		.append(" left JOIN scene G ON A.scene_id = G.scene_id")
		.append(" left JOIN languages H ON A.languages_id = H.languages_id");
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while (rs.next()) {
				
				Integer musicId = rs.getInt("music_id");
				String musicName = rs.getString("music_name");
				String initials  = rs.getString("initials");
				Integer singgerId= rs.getInt("singger_id");
				String singgerName  = rs.getString("singger_name");
				Integer albumId = rs.getInt("album_id");
				String albumName  = rs.getString("album_name");
				Integer styleId = rs.getInt("style_id");
				String styleName  = rs.getString("style_name");
				String releaseTime = rs.getString("release_time");
				Integer themeId = rs.getInt("theme_id");
				String themeName  = rs.getString("theme_name");
				Integer moodId = rs.getInt("mood_id");
				String moodName  = rs.getString("mood_name");
				Integer sceneId = rs.getInt("scene_id");
				String sceneName  = rs.getString("scene_name");
				Integer languagesId = rs.getInt("languages_id");
				String languagesName  = rs.getString("languages_name");
				String createTim = rs.getString("create_time");
				Integer likeNumber = rs.getInt("like_number");
				Integer playVolume = rs.getInt("play_volume");
				String musicImgPath = rs.getString("music_img_path");
				
				MusicInfo musicInfo = new MusicInfo();
				musicInfo = new MusicInfo();
				musicInfo.setMusicId(musicId);
				musicInfo.setMusicName(musicName);
				musicInfo.setInitials(initials);
				Singger singger =  new Singger();
				singger.setSinggerId(singgerId);
				singger.setSinggerName(singgerName);
				musicInfo.setSingger(singger );
				Album album = new Album();
				album.setAlbumId(albumId);
				album.setAlbumName(albumName);
				musicInfo.setAlbum(album);
				MusicStyle musicStyle = new MusicStyle();
				musicStyle.setStyleId(styleId);
				musicStyle.setStyleName(styleName);
				musicInfo.setMusicStyle(musicStyle);
				Theme theme = new Theme();
				theme.setThemeId(themeId);
				theme.setThemeName(themeName);
				musicInfo.setTheme(theme);
				Mood mood = new Mood();
				mood.setMoodId(moodId);
				mood.setMoodName(moodName);
				musicInfo.setMood(mood);
				Scene scene = new Scene();
				scene.setSceneId(sceneId);
				scene.setSceneName(sceneName);
				musicInfo.setScene(scene);
				Languages languages = new Languages();
				languages.setLanguagesId(languagesId);
				languages.setLanguagesName(languagesName);
				musicInfo.setLanguages(languages);
				musicInfo.setReleaseTime(releaseTime);
				musicInfo.setCreateTim(createTim);
				musicInfo.setLikeNumber(likeNumber);
				musicInfo.setPlayVolume(playVolume);
				musicInfo.setMusicImgPath(musicImgPath);
				
				list.add(musicInfo);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return list;
	}


	@Override
	public List<MusicInfo> findByKeyWord(String keyWord, int pageNum, int pageSize) {
		 List<MusicInfo> listMusicInfo = new ArrayList<MusicInfo>();
			
			StringBuilder sb = new StringBuilder();
			sb.append("select ")
			.append(" A.music_id,")
			.append(" A.music_name,")
			.append(" A.music_mp3_path,")
			.append(" A.duration,")
			.append(" B.singger_id,")
			.append(" B.singger_name,")
			.append(" B.singger_name,")
			.append(" C.video_id,")
			.append(" C.video_name,")
			.append(" D.album_id,")
			.append(" D.album_name")
			.append(" from music_info A")
			.append(" left join singger B on A.singger_id = B.singger_id")
			.append(" left join music_video C on A.music_id = C.music_id")
			.append(" left join album D on A.album_id = D.album_id");
			
			// 动态SQL
			sb.append(" where 1 = 1");
			
			if (keyWord != null && !"".equals(keyWord)) {
				sb.append(" and A.music_name like '%" + keyWord + "%' or B.singger_name like '%"+ keyWord + "%'");
			}
			
					
					
					
					// 进行分页  select * from 表  where... limit a,b;
					sb.append(" limit " +  (pageNum - 1) * pageSize + "," + pageSize);

			ResultSet rs = DBUtil.query(sb.toString());
			try {
				while (rs.next()) {
					//获取数据 
					Integer musicId = rs.getInt("music_id");
					 String musicName = rs.getString("music_name");
					 String musicMp3Path = rs.getString("music_mp3_path");
					 String duration = rs.getString("duration");
					 Integer singgerId = rs.getInt("singger_id");
					 String singgerName = rs.getString("singger_name");
					 Integer videoId = rs.getInt("video_id");
					 String videoName = rs.getString("video_name");
					 Integer albumId = rs.getInt("album_id");
					 String albumName = rs.getString("album_name");
					
					 //歌曲对象
					 MusicInfo musicInfo = new MusicInfo();
					 musicInfo.setMusicId(musicId);
					 musicInfo.setMusicName(musicName);	
					 musicInfo.setMusicMp3Path(musicMp3Path);
					 musicInfo.setDuration(duration);
					 
					 Singger singger = new Singger();
					singger.setSinggerId(singgerId);	
					singger.setSinggerName(singgerName);
					musicInfo.setSingger(singger);
					
					MusicVideo musicVideo = new MusicVideo();
					musicVideo.setVideoId(videoId);
					musicVideo.setVideoName(videoName);
					musicInfo.setMusicVideo(musicVideo);
					
					Album album =new Album();
					album.setAlbumId(albumId);
					album.setAlbumName(albumName);
					musicInfo.setAlbum(album);
					
					//集合
					listMusicInfo.add(musicInfo);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return listMusicInfo;
	}

	@Override
	public int countMusicByKeyWord(String keyWord) {
		int count = 0;
		StringBuilder sb = new StringBuilder();
		sb.append("select ")
		.append(" count(*)")
		.append(" from music_info A")
		.append(" left join singger B on A.singger_id = B.singger_id")
		.append(" left join music_video C on A.music_id = C.music_id")
		.append(" left join album D on A.album_id = D.album_id");
		
		// 动态SQL
		sb.append(" where 1 = 1");
		
		if (keyWord != null && !"".equals(keyWord)) {
			sb.append(" and A.music_name like '%" + keyWord + "%' or B.singger_name like '%"+ keyWord + "%'");
		}
		ResultSet rs = DBUtil.query(sb.toString());
		try {
			while (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

	


	@Override
	public List<MusicInfo> findHalfYearMusic(String languagesName) {
		List<MusicInfo> musicInfos = new ArrayList<MusicInfo>();
		StringBuilder sb = new StringBuilder();
		sb.append("select A.music_id,")
		.append("A.music_name,")
		.append("A.music_img_path,")
		.append("A.music_mp3_path,")
		.append("A.duration,")
		.append("B.singger_name")
		.append(" from music_info A INNER JOIN singger B on A.singger_id = B.singger_id")
		.append(" WHERE release_time>DATE_SUB(CURDATE(), INTERVAL 6 MONTH)");
		
		
		if ("国语".equals(languagesName)) {
			sb.append(" and languages_id = 1");
		}
		else if ("英语".equals(languagesName)) {
			sb.append(" and languages_id = 2");
		}else if ("日语".equals(languagesName)) {
			sb.append(" and languages_id = 3");
		}else if ("韩语".equals(languagesName)) {
			sb.append(" and languages_id = 4");
		}else if ("西班牙语".equals(languagesName)) {
			sb.append(" and languages_id = 5");
		}

		ResultSet rs = DBUtil.query(sb.toString());
		try {
			while (rs.next()) {
				MusicInfo musicInfo = new MusicInfo();
				int musicId = rs.getInt("music_id");
				String musicName = rs.getString("music_name");
				String musicImgPath = rs.getString("music_img_path");
				String musicMp3Path = rs.getString("music_mp3_path");
				String singgerName = rs.getString("singger_name");
				String duration = rs.getString("duration");
				
				musicInfo.setMusicId(musicId);
				musicInfo.setMusicName(musicName);
				musicInfo.setMusicImgPath(musicImgPath);
				musicInfo.setMusicMp3Path(musicMp3Path);
				musicInfo.setDuration(duration);
				Singger singger =  new Singger();
				singger.setSinggerName(singgerName);
				musicInfo.setSingger(singger );
				
				musicInfos.add(musicInfo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return musicInfos;
	}


	@Override
	public List<MusicInfo> findListByCondition(Integer condition) {
		List<MusicInfo> list = new ArrayList<MusicInfo>();
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ")
		  .append("A.music_id,A.music_name,B.singger_name")
		  .append(" FROM music_info A LEFT JOIN singger B ON A.singger_id = B.singger_id");
		if (condition==0) { // 流行歌曲
			sb.append(" ORDER BY play_volume DESC LIMIT 3");
		} else if (condition==1) { // 热歌
			sb.append(" ORDER BY like_number DESC LIMIT 3");
		} else if (condition==2) { // 新歌3个月
			sb.append(" WHERE create_time>DATE_SUB(CURDATE(), INTERVAL 3 MONTH) LIMIT 3");
		} else if (condition==3) { // 内地
			sb.append(" WHERE B.region_id = 1 ORDER BY like_number DESC LIMIT 3");
		} else if (condition==4) { // 日本
			sb.append(" WHERE B.region_id = 4 ORDER BY like_number DESC LIMIT 3");
		}
		ResultSet rs = DBUtil.query(sb.toString());
		try {
			while (rs.next()) {
				Integer musicId = rs.getInt("music_id");
				String musicName = rs.getString("music_name");
				String singgerName = rs.getString("singger_name");
				
				MusicInfo musicInfo = new MusicInfo();
				musicInfo.setMusicId(musicId);
				musicInfo.setMusicName(musicName);
				Singger singger = new Singger();
				singger.setSinggerName(singgerName);
				musicInfo.setSingger(singger);
				
				list.add(musicInfo);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<Map<String, Object>> findStyleCount() {
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT B.style_name,COUNT(*)")
		.append(" FROM music_info A LEFT JOIN music_style B ON A.style_id = B.style_id GROUP BY A.style_id");
		
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while (rs.next()) {
				String styleName = rs.getString("style_name");
				BigDecimal countLikeNumber = rs.getBigDecimal(2);
				
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("styleName", styleName);
				map.put("countLikeNumber", countLikeNumber);
				
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	

}
